"""
SQLAlchemy数据库模型定义
"""
from sqlalchemy import create_engine, Column, Integer, String, Boolean, Float, Text, DateTime, BigInteger, ForeignKey, SmallInteger, DECIMAL, Date, Index, text, JSON
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
from typing import TYPE_CHECKING, Optional, List
import os

if TYPE_CHECKING:
    from biz.goods_score_analyzer import ScoreResult

# 创建基类
Base = declarative_base()

def get_db_path():
    """获取数据库文件路径"""
    import sys
    if getattr(sys, 'frozen', False):
        # 如果是打包后的程序
        base_dir = os.path.dirname(sys.executable)
    else:
        # 如果是开发环境
        base_dir = os.path.dirname(os.path.dirname(os.path.dirname(__file__)))
    
    db_dir = os.path.join(base_dir, "data")
    if not os.path.exists(db_dir):
        os.makedirs(db_dir)
    return os.path.join(db_dir, "uudata.db")

class ScanAccount(Base):
    """扫描账号表"""
    __tablename__ = 'scan_account'
    
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    tags = Column(String)
    uu_token = Column(String)
    device_id = Column(String)
    alive = Column(Integer, default=0)
    status = Column(Integer, default=1)
    locked = Column(Integer, default=0)
    locked_at = Column(Integer, default=0)
    safe_verify = Column(Integer, default=0)
    uu_user_id = Column(Integer)
    avatar = Column(String)
    nickname = Column(String)
    invalid_desc = Column(String)
    steam_id = Column(String)
    min_interval = Column(Integer, default=10)
    create_at = Column(String)
    update_at = Column(Integer)
    alive_times = Column(Integer, default=0)
    uk = Column(String)  # 添加uk字段
    device_info = Column(JSON)  # 添加device_info字段，使用JSON类型存储设备详细信息

class Task(Base):
    """任务表"""
    __tablename__ = 'tasks'
    
    id = Column(Integer, primary_key=True)
    goods_id = Column(Integer, nullable=False)
    name = Column(String, nullable=False)
    hash_name = Column(String)  # 饰品哈希名称
    wear = Column(String)  # 磨损范围
    rarity = Column(String)  # 稀有度
    quality = Column(String)  # 品质
    exterior = Column(String)  # 外观
    
    # 评分相关字段
    score = Column(Float)  # 投资评分
    liquidity_score = Column(Float)  # 流动性评分
    price_advantage_score = Column(Float)  # 价格优势评分
    stability_score = Column(Float)  # 稳定性评分
    supply_score = Column(Float)  # 供需评分
    daily_volume = Column(Float)  # 日均成交量
    
    # 价格相关字段
    sell_min_price = Column(Float)
    sell_max_price = Column(Float)
    suggested_snipe_price = Column(Float)  # 建议捡漏价格
    theoretical_max_buy_price = Column(Float)  # 理论最高买入价格
    target_sell_price = Column(Float)  # 目标售价
    profit_rate = Column(Float, default=0)  # 利润率
    min_profit_rate = Column(Float, default=0)  # 最低利润率
    pricing_type = Column(Integer, default=0)  # 扫货定价方式类型(0:固定价格 1：动态利润率  2：固定利润率)
    historical_success_rate = Column(Text)  # 历史成功率(JSON)
    price_range_compactness = Column(Float)  # 价格区间紧凑度
    volatility = Column(Float)  # 波动率
    trend_analysis = Column(Text)  # 趋势分析(JSON)
    historical_price_range = Column(Text)  # 历史价格(JSON)
    cluster_analysis = Column(Text)  # 聚类分析(JSON)
    
    # 其他配置字段
    tags = Column(Text)
    filter_conf = Column(Text)
    buy_price_limit = Column(Float)
    update_price_auto = Column(Integer, default=0)
    update_price_conf = Column(Text)
    buy_num_limit = Column(Integer)
    bought_num = Column(Integer, default=0)
    min_interval = Column(Integer)
    status = Column(Integer, default=1)
    goods_url = Column(String)
    sell_num = Column(Integer)
    filter_min_price = Column(Float)
    create_at = Column(String)
    update_at = Column(Integer)
    buying_num = Column(Integer, default=0)
    disabled = Column(Integer, default=0)
    disabled_desc = Column(String)
    disabled_at = Column(Integer)
    disabled_until = Column(Integer)
    goods_task_num = Column(Integer, default=0)
    goods_task_status = Column(Integer, default=0)

class OrderRecord(Base):
    """订单记录表"""
    __tablename__ = 'order_record'
    
    id = Column(Integer, primary_key=True)
    account_id = Column(Integer)
    account_name = Column(String)
    script_id = Column(Integer)
    script_name = Column(String)
    task_id = Column(Integer)
    task_name = Column(String)
    order_no = Column(String)
    pay_order_no = Column(String)
    wait_payment_data_no = Column(String)
    assetid = Column(String)
    name = Column(String)
    price = Column(Float)
    game = Column(String)
    goods_id = Column(Integer)
    market_hash_name = Column(String)
    paintwear = Column(Float)
    expect_income = Column(Float)
    buy_status = Column(Integer, default=0)
    pay_type = Column(Integer)
    offer_status = Column(Integer, default=0)
    offer_times = Column(Integer, default=0)
    offer_fail_desc = Column(String)
    create_at = Column(DateTime, default=datetime.utcnow)
    update_at = Column(DateTime, onupdate=datetime.utcnow)
    buy_at = Column(DateTime)
    offer_at = Column(DateTime)

class OrderAccount(Base):
    """订单账号表"""
    __tablename__ = 'order_account'
    
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=True)
    uu_token = Column(String)
    device_id = Column(String)
    steam_cookie = Column(String)
    status = Column(Integer, default=1)
    uu_alive = Column(Integer, default=0)
    steam_alive = Column(Integer, default=0)
    safe_verify = Column(Integer, default=0)
    uu_user_id = Column(Integer)
    avatar = Column(String)
    nickname = Column(String)
    uu_invalid_desc = Column(String)
    steam_invalid_desc = Column(String)
    steam_id = Column(String)
    uu_steam_id = Column(String)
    balance_limit = Column(Float, default=0.1)
    balance_monitor = Column(Integer, default=1)
    buy_fail_monitor = Column(Integer, default=0)
    steam_login_type = Column(String)
    steam_account_name = Column(String)
    steam_password = Column(String)
    steam_api_key = Column(String)
    steam_mafile = Column(Text)
    pay_type = Column(Integer, default=1)
    pay_methods = Column(String)
    buy_method = Column(Integer, default=1)
    create_at = Column(String)
    update_at = Column(Integer)
    uu_alive_times = Column(Integer, default=0)
    uk = Column(String)
    device_info = Column(JSON)  # 添加device_info字段，使用JSON类型存储设备详细信息

class Script(Base):
    """脚本表"""
    __tablename__ = 'script'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    is_auto_offer = Column(Integer, default=0)
    order_account_id = Column(Integer)
    node_index = Column(Integer)
    account_count = Column(Integer, default=0)
    task_count = Column(Integer, default=0)
    status = Column(Integer, default=1)
    run_status = Column(Integer, default=0)
    start_time = Column(String)
    end_time = Column(String)
    latest_runtime = Column(BigInteger)
    latest_times = Column(Integer, default=0)
    update_at = Column(BigInteger)
    create_at = Column(String)
    fail_desc = Column(String)

class ScriptScanAccount(Base):
    """脚本扫描账号关联表"""
    __tablename__ = 'script_scan_account'
    
    id = Column(Integer, primary_key=True)
    script_id = Column(Integer, ForeignKey('script.id'))
    scan_account_id = Column(Integer, ForeignKey('scan_account.id'))

class ScriptTask(Base):
    """脚本任务关联表"""
    __tablename__ = 'script_task'
    
    id = Column(Integer, primary_key=True)
    script_id = Column(Integer, ForeignKey('script.id'))
    task_id = Column(Integer, ForeignKey('tasks.id'))
    status = Column(Integer, default=0)
    result = Column(Text)
    error_message = Column(String)
    start_time = Column(String)
    end_time = Column(String)
    create_at = Column(String)
    update_at = Column(BigInteger)

class UuCons(Base):
    """配置表"""
    __tablename__ = 'uu_cons'
    
    id = Column(Integer, primary_key=True)
    conskey = Column(String, nullable=False, unique=True)
    consvalue = Column(String)
    createt = Column(Integer)
    updatet = Column(Integer)

class CommodityHistoryData(Base):
    """饰品历史交易数据表"""
    __tablename__ = 'commodity_history_data'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    template_id = Column(Integer, nullable=False)
    his_type = Column(Integer, nullable=False)
    wear_value = Column(DECIMAL(10,8), nullable=False)
    trade_time = Column(Integer, nullable=False)
    trade_date = Column(Date, nullable=False)
    price = Column(DECIMAL(10,2), nullable=False)
    create_time = Column(DateTime, nullable=False)
    update_time = Column(DateTime, nullable=False)

    __table_args__ = (
        Index('idx_template_date', 'template_id', 'trade_date'),
        Index('idx_template_type', 'template_id', 'his_type'),
    )

class ScanRecord(Base):
    """扫描记录表"""
    __tablename__ = 'scan_records'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    task_id = Column(Integer, nullable=False, index=True)
    script_id = Column(Integer, nullable=True)
    goods_id = Column(String(50), nullable=False, index=True)
    goods_name = Column(String(255), nullable=True)
    account_id = Column(Integer, nullable=False, index=True)
    
    # 时间信息
    thread_id = Column(String(50), nullable=False)
    thread_create_time = Column(DateTime, nullable=False)
    wait_start_time = Column(DateTime, nullable=False)
    wait_end_time = Column(DateTime, nullable=False)
    wait_duration = Column(Float, nullable=False)  # 毫秒
    scan_start_time = Column(DateTime, nullable=False)
    scan_end_time = Column(DateTime, nullable=False)
    scan_duration = Column(Float, nullable=False)  # 毫秒
    thread_end_time = Column(DateTime, nullable=False)
    total_duration = Column(Float, nullable=False)  # 毫秒
    
    # 扫描配置
    price_limit = Column(Float, nullable=False)
    min_paintwear = Column(String(10), nullable=True)
    max_paintwear = Column(String(10), nullable=True)
    remaining_purchase = Column(Integer, nullable=False)
    
    # 扫描结果
    api_response_code = Column(Integer, nullable=True)
    api_response_time = Column(Float, nullable=True)  # 毫秒
    found_items = Column(Integer, nullable=True)
    min_price = Column(Float, nullable=True)
    max_price = Column(Float, nullable=True)
    avg_price = Column(Float, nullable=True)
    selling_count = Column(Integer, nullable=True)  # 在售数量
    scan_status = Column(String(20), nullable=False)  # success, failed, limited
    error_message = Column(Text, nullable=True)
    
    # 额外信息
    extra_data = Column(JSON, nullable=True)  # 存储任何额外的信息
    price_list = Column(JSON, nullable=True)  # 新增价格列表字段
    purchase_success_count = Column(Integer, nullable=True)  # 扫货成功数量
    create_at = Column(DateTime, nullable=False, default=datetime.now)
    update_at = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
    
    def __repr__(self):
        return f"<ScanRecord(id={self.id}, task_id={self.task_id}, goods_id={self.goods_id})>"

class ScanRecordPriceAnalysis(Base):
    """扫描记录价格分析表"""
    __tablename__ = 'scan_record_price_analysis'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    goods_id = Column(Integer, nullable=False)
    goods_name = Column(String(255))
    hash_name = Column(String(255))    # 饰品哈希名称
    wear = Column(String(50))          # 磨损范围
    exterior = Column(String(50))      # 外观
    rarity = Column(String(50))        # 稀有度
    quality = Column(String(50))       # 品质
    
    analysis_time = Column(DateTime, nullable=False)
    analysis_period = Column(String(10), nullable=False)  # 分析周期（24h/7d）
    
    # 统计数据中最早的记录时间
    earliest_record_time = Column(DateTime, nullable=True)  
    # 统计数据中最晚的记录时间
    latest_record_time = Column(DateTime, nullable=True)    
    # 异常低价出现的时间分布分析结果
    abnormal_price_time_distribution = Column(JSON, nullable=True)  
    # 产品活动时间分布分析结果
    activity_time_distribution = Column(JSON, nullable=True)        
    
    price_variance = Column(Float)              # 价格离散度
    abnormal_prices = Column(JSON)             # 异常价格列表
    common_sell_price = Column(Float)          # 常规售价
    normal_price = Column(Float)               # 普通售价
    historical_low = Column(Float)             # 历史最低价
    price_distribution = Column(JSON)          # 价格分布统计
    selling_count = Column(Integer)            # 在售数量
    record_count = Column(Integer)             # 统计数据条数
    top_price_list = Column(JSON, nullable=True)  # 数量前几位的价格列表
    scan_reference_price = Column(Float, nullable=True)  # 扫货参考价格
    
    create_at = Column(DateTime, nullable=False, default=datetime.now)
    update_at = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
    
    __table_args__ = (
        Index('idx_goods_period', 'goods_id', 'analysis_period', unique=True),
        Index('idx_goods_time', 'goods_id', 'analysis_time'),
    )
    
    def __repr__(self):
        return f"<ScanRecordPriceAnalysis(id={self.id}, goods_id={self.goods_id}, analysis_period={self.analysis_period})>"

class GoodsScoreAnalysis(Base):
    """商品评分分析表"""
    __tablename__ = 'goods_score_analysis'
    
    id = Column(Integer, primary_key=True)
    goods_id = Column(String(50), nullable=False, index=True)
    
    # 主要评分
    price_stability_score = Column(Float, default=0)  # 价格稳定性得分
    profit_potential_score = Column(Float, default=0)  # 利润空间得分
    trading_frequency_score = Column(Float, default=0)  # 交易频率得分
    price_trend_score = Column(Float, default=0)  # 价格趋势得分
    time_activity_score = Column(Float, default=0)  # 时间活跃度得分
    historical_success_score = Column(Float, default=0)  # 历史成功率得分
    total_score = Column(Float, default=0)  # 总分
    grade = Column(String(2), default='C')  # 等级
    
    # 价格稳定性子评分
    stability_cv_score = Column(Float, default=0)  # 稳定性-变异系数评分
    stability_volatility_score = Column(Float, default=0)  # 稳定性-波动率评分

    # 利润空间子评分
    profit_rate_score = Column(Float, default=0)  # 利润-利润率评分
    profit_frequency_score = Column(Float, default=0)  # 利润-频率评分
    profit_time_weighted_score = Column(Float, default=0)  # 利润-时间衰减评分

    # 交易频率子评分
    frequency_activity_score = Column(Float, default=0)  # 频率-活动频率评分
    frequency_selling_change_score = Column(Float, default=0)  # 频率-在售数量变化得分
    frequency_price_fluctuation_score = Column(Float, default=0)  # 频率-价格波动得分
        
    # 价格趋势子评分
    trend_direction_score = Column(Float, default=0)  # 趋势-方向和强度得分
    trend_range_score = Column(Float, default=0)  # 趋势-价格变化幅度得分
    trend_stability_score = Column(Float, default=0)  # 趋势-稳定性得分

    # 附加信息
    last_7d_trade_count = Column(Integer, default=0)  # 最近7天交易次数
    last_7d_avg_price = Column(Float, default=0)  # 最近7天平均价格
    price_variance = Column(Float, default=0)  # 价格离散度
    success_rate = Column(Float, default=0)  # 成功率
        
    create_at = Column(DateTime, default=datetime.now)  # 创建时间
    update_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)  # 更新时间

    __table_args__ = (
        Index('idx_goods_id', 'goods_id', unique=True),
    )

class ProfitRatePriceRecord(Base):
    """利润率和价格变动记录表"""
    __tablename__ = 'profit_rate_price_records'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    goods_id = Column(Integer, nullable=False, index=True)  # 饰品ID
    goods_name = Column(String(255), nullable=True)  # 饰品名称
    hash_name = Column(String(255))  # 饰品哈希名称
    profit_rate = Column(Float, nullable=False)  # 利润率
    buy_price = Column(Float, nullable=False)  # 扫货价格
    target_sell_price = Column(Float, nullable=False)  # 目标售价
    update_time = Column(DateTime, nullable=False, default=datetime.now)  # 变动更新时间
    operator = Column(String(50), nullable=True)  # 操作人
    remark = Column(String(255), nullable=True)  # 备注
    modify_type = Column(Integer, default=1)  # 修改方式：1-自动调整 2-人为调整
    pricing_type = Column(Integer, default=1)  # 扫货定价类型：0-按固定价 1：动态利润率 2：固定利润率
    create_at = Column(DateTime, nullable=False, default=datetime.now)  # 创建时间
    update_at = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)  # 更新时间
    
    # 新增字段
    base_snipe_price = Column(Float, nullable=True)  # 基础扫货价（原始计算的扫货价格，未经过约束调整）
    common_sell_price = Column(Float, nullable=True)  # 常卖价（市场常见价格）
    normal_price = Column(Float, nullable=True)  # 普通售价（官方最低价）
    adjustment_factor = Column(Float, nullable=True)  # 最终应用的价格调整系数
    price_variance = Column(Float, nullable=True)  # 商品价格的波动性指标
    upper_bound_price = Column(Float, nullable=True)  # 调整后的上限约束价格
    lower_bound_price = Column(Float, nullable=True)  # 调整后的下限约束价格
    
    # 动态价格调整新增字段
    min_required_gap = Column(Float, nullable=True)  # 计算得到的动态最小间距比例值
    actual_price_gap_ratio = Column(Float, nullable=True)  # 实际价格间距比例
    history_data_count = Column(Integer, nullable=True)  # 动态间距计算使用的历史数据记录数量
    reference_price_source = Column(String(20), nullable=True)  # 扫货参考价来源（原始/常卖价替代）
    adjustment_strategy = Column(String(30), nullable=True)  # 采用的价格调整策略（原始扫货价/下限约束/上限约束）
    price_change_percentage = Column(Float, nullable=True)  # 调整前后的价格变化幅度（百分比）
    estimated_profit_rate = Column(Float, nullable=True)  # 新增字段：推算利润率（基于历史数据）
    
    # 价格趋势信息
    price_trend_direction = Column(String(20), nullable=True)  # 价格趋势方向（上升、下降、稳定）
    price_trend_slope = Column(Float, nullable=True)  # 价格趋势斜率
    price_trend_strength = Column(Float, nullable=True)  # 价格趋势强度
    
    # 异常值过滤信息
    outlier_filter_applied = Column(Boolean, nullable=True)  # 是否应用了异常值过滤
    outlier_count = Column(Integer, nullable=True)  # 检测到的异常值数量
    
    # 参考价格选择逻辑
    reference_price_selection_method = Column(String(30), nullable=True)  # 参考价格选择方法（百分位数、中位数等）
    reference_price_percentile = Column(Float, nullable=True)  # 如果使用百分位数，记录使用的具体百分位
    
    # 价格间距判断详情
    price_gap_absolute = Column(Float, nullable=True)  # 扫货参考价和下限价格的绝对间距值
    gap_check_result = Column(Boolean, nullable=True)  # 间距检查结果（通过/不通过）
    
    # 最小参考价格信息
    min_reference_price = Column(Float, nullable=True)  # 用于计算上限约束的最小参考价格
    min_reference_price_source = Column(String(30), nullable=True)  # 最小参考价格的来源（当前扫货参考价/历史中位数/历史百分位数）
    
    # 历史数据统计信息
    history_data_time_span = Column(Integer, nullable=True)  # 历史数据的时间跨度（天数）
    history_data_first_date = Column(DateTime, nullable=True)  # 历史数据的最早日期
    history_data_last_date = Column(DateTime, nullable=True)  # 历史数据的最晚日期（不含当天）
    
    # 决策路径跟踪
    decision_path = Column(String(255), nullable=True)  # 记录价格调整过程中的决策路径
    
    def __repr__(self):
        return f"<ProfitRatePriceRecord(id={self.id}, goods_id={self.goods_id}, profit_rate={self.profit_rate}, update_time={self.update_time})>"

class CSGOKlineAnalysis(Base):
    """CSGO K线分析数据表"""
    __tablename__ = 'csgo_kline_analysis'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    item_id = Column(String(50), nullable=False, index=True)
    item_name = Column(String(255), nullable=False)
    timestamp = Column(DateTime, nullable=False, default=datetime.now)
    analysis_period = Column(String(10), nullable=False)  # 'hour', 'day', 'week', 'combined'
    
    # 价格数据
    current_price = Column(Float, nullable=False)
    
    # 基本趋势信息
    overall_trend = Column(String(20))  # 综合趋势
    
    # 关键技术指标
    support_levels = Column(JSON)  # 支撑位数组
    resistance_levels = Column(JSON)  # 阻力位数组
    
    # 投资决策信息
    investment_score = Column(Float)  # 投资评分(0-100)
    recommendation = Column(String(20))  # 建议操作(buy/sell/hold)
    expected_return = Column(Float)  # 预期收益率
    risk_reward_ratio = Column(Float)  # 风险收益比
    target_buy_price = Column(Float)  # 目标买入价格
    target_sell_price = Column(Float)  # 目标卖出价格
    stop_loss_price = Column(Float)  # 止损价格
    is_worth_investing = Column(Boolean)  # 是否值得投资
    simple_advice = Column(String(255))  # 简单投资建议
    
    # 文件引用
    analysis_details = Column(JSON)  # 分析详情，包含JSON文件路径
    
    # 时间戳
    create_at = Column(DateTime, nullable=False, default=datetime.now)
    update_at = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
    expiry_date = Column(DateTime)  # 数据过期时间
    
    __table_args__ = (
        Index('idx_item_period_timestamp', 'item_id', 'analysis_period', 'timestamp'),
        Index('idx_period_timestamp', 'analysis_period', 'timestamp'),
    )
    
    def __repr__(self):
        return f"<CSGOKlineAnalysis(id={self.id}, item_id={self.item_id}, period={self.analysis_period}, timestamp={self.timestamp})>"

# 创建数据库引擎
engine = create_engine(
    f'sqlite:///{get_db_path()}',
    connect_args={
        'timeout': 30,  # 连接超时时间
        'check_same_thread': False,  # 允许跨线程访问
        'uri': True  # 启用URI模式
    }
)

# 创建会话工厂
Session = sessionmaker(bind=engine)

def init_db():
    """初始化数据库"""
    try:
        # 创建所有表
        Base.metadata.create_all(engine)
        print("数据库表创建成功")
    except Exception as e:
        print(f"创建数据库表时发生错误: {str(e)}")

def get_session():
    """获取数据库会话"""
    return Session()

# 初始化数据库
if __name__ == "__main__":
    init_db()
